var express = require('express');
var router = express.Router();
const connection = require('./db/conn'); // 引入数据库连接
const { v4: uuidv4 } = require('uuid'); // 引入UUID生成器

/**
 * UUID格式验证函数
 * @param {string} uuid - 要验证的UUID字符串
 * @returns {boolean} 是否为有效的UUID格式
 */
function isValidUUID(uuid) {
    const uuidRegex = /^[0-9a-f]{8}-[0-9a-f]{4}-[1-5][0-9a-f]{3}-[89ab][0-9a-f]{3}-[0-9a-f]{12}$/i;
    return typeof uuid === 'string' && uuidRegex.test(uuid);
}

/**
 * 文档管理模块 - 完整的CRUD操作
 * 基于documents表设计实现知识库文档的增删改查功能
 */

/**
 * 创建文档 - POST /documents
 * @description 创建新的文档记录，使用UUID生成唯一ID
 * @body {Object} document - 文档信息
 * @body {string} document.name - 文档名称 (必填)
 * @body {string} document.version - 版本号 (必填)
 * @body {string} document.content - 文档内容 (必填)
 * @body {string} [document.version_description] - 版本描述
 * @body {string} [document.category] - 文档分类
 * @body {boolean} [document.is_published=false] - 是否发布
 * @body {string} [document.created_by] - 创建人
 * @body {string} [document.tags] - 标签，多个用逗号分隔
 * @body {string} [document.file_path] - 关联文件路径
 * @returns {Object} 创建结果和文档UUID
 */
router.post('/', (req, res) => {
    const {
        name,
        version = '1.0.0',
        version_description,
        content,
        category,
        is_published = false,
        created_by,
        tags,
        file_path,
        category_id,
    } = req.body;

    // 参数验证
    if (!category_id) {
        return res.json({
            success: false,
            message: '分类为必填项'
        });
    }

    // 生成UUID作为文档ID
    const documentId = uuidv4();

    const sql = `
        INSERT INTO documents (
            id, name, version, version_description, content, category, 
            is_published, created_by, updated_by, tags, file_path,category_id
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?)
    `;

    const values = [
        documentId, // 使用生成的UUID
        name|| null,
        version|| null,
        version_description || null,
        content|| null,
        category || null,
        is_published,
        created_by || null,
        created_by || null, // updated_by 初始值与 created_by 相同
        tags || null,
        file_path || null,
        category_id,
    ];

    connection.query(sql, values, (err, result) => {
        if (err) {
            console.error('创建文档失败:', err);
            return res.status(500).json({
                success: false,
                message: '创建文档失败',
                error: err.message
            });
        }

        res.status(201).json({
            success: true,
            message: '文档创建成功',
            data: {
                id: documentId, // 返回UUID
                name,
                version
            }
        });
    });
});

/**
 * 获取文档列表 - GET /documents
 * @description 分页获取文档列表，支持筛选和搜索
 * @query {number} [page=1] - 页码
 * @query {number} [limit=10] - 每页数量
 * @query {string} [category] - 按分类名称筛选
 * @query {string} [category_id] - 按分类ID筛选
 * @query {boolean} [is_published] - 按发布状态筛选
 * @query {string} [keyword] - 关键词搜索（搜索名称、内容、标签）
 * @query {string} [version] - 按版本筛选
 * @returns {Object} 文档列表和分页信息
 */
router.get('/', (req, res) => {
    const {
        page = 1,
        limit = 10,
        category,
        category_id,
        is_published,
        keyword,
        version,
    } = req.query;


    // 验证和转换数字参数
    const pageNum = parseInt(page);
    const limitNum = parseInt(limit);
    
    if (isNaN(pageNum) || pageNum < 1) {
        return res.status(400).json({
            success: false,
            message: '页码参数无效'
        });
    }
    
    if (isNaN(limitNum) || limitNum < 1 || limitNum > 1000) {
        return res.status(400).json({
            success: false,
            message: '每页数量参数无效（范围：1-1000）'
        });
    }

    const offset = (pageNum - 1) * limitNum;
    let whereConditions = [];
    let queryParams = [];

    // 构建查询条件
    if (category) {
        // 智能判断category参数：如果是UUID格式，则查询category_id；否则查询category字段
        if (isValidUUID(category)) {
            whereConditions.push('category_id = ?');
            queryParams.push(category);
        } else {
            whereConditions.push('category = ?');
            queryParams.push(category);
        }
    }

    if (category_id) {
        // category_id 可能是 UUID 字符串或数字，直接使用原值
        whereConditions.push('category_id = ?');
        queryParams.push(category_id);
    }

    if (is_published !== undefined) {
        whereConditions.push('is_published = ?');
        queryParams.push(is_published === 'true');
    }

    if (version) {
        whereConditions.push('version = ?');
        queryParams.push(version);
    }

    if (keyword) {
        whereConditions.push('(name LIKE ? OR content LIKE ? OR tags LIKE ?)');
        const searchTerm = `%${keyword}%`;
        queryParams.push(searchTerm, searchTerm, searchTerm);
    }

    const whereClause = whereConditions.length > 0 ? `WHERE ${whereConditions.join(' AND ')}` : '';
    

    // 查询总数
    const countSql = `SELECT COUNT(*) as total FROM documents ${whereClause}`;
    
    
    connection.query(countSql, queryParams, (err, countResult) => {
        if (err) {
            console.error('查询文档总数失败:', err);
            return res.status(500).json({
                success: false,
                message: '查询失败',
                error: err.message
            });
        }

        const total = countResult[0].total;

        // 查询文档列表
        const listSql = `
            SELECT id, name, version, version_description, category, category_id,
                   is_published, created_at, updated_at, created_by, 
                   updated_by, tags, file_path
            FROM documents 
            ${whereClause}
            ORDER BY updated_at DESC, created_at DESC
            LIMIT ? OFFSET ?
        `;

        const listParams = [...queryParams, limitNum, offset];
        

        connection.query(listSql, listParams, (err, documents) => {
            if (err) {
                console.error('查询文档列表失败:', err);
                return res.status(500).json({
                    success: false,
                    message: '查询失败',
                    error: err.message
                });
            }


            res.json({
                success: true,
                data: {
                    documents,
                    pagination: {
                        current_page: pageNum,
                        per_page: limitNum,
                        total,
                        total_pages: Math.ceil(total / limitNum)
                    }
                }
            });
        });
    });
});

/**
 * 获取单个文档详情 - GET /documents/:id
 * @description 根据UUID获取文档完整信息
 * @param {string} id - 文档UUID
 * @returns {Object} 文档详细信息
 */
router.get('/:id', (req, res) => {
    const { id } = req.params;

    // UUID格式验证
    if (!isValidUUID(id)) {
        return res.status(400).json({
            success: false,
            message: '无效的文档ID格式'
        });
    }

    const sql = 'SELECT * FROM documents WHERE id = ?';

    connection.query(sql, [id], (err, result) => {
        if (err) {
            console.error('查询文档详情失败:', err);
            return res.status(500).json({
                success: false,
                message: '查询失败',
                error: err.message
            });
        }

        if (result.length === 0) {
            return res.status(404).json({
                success: false,
                message: '文档不存在'
            });
        }

        res.json({
            success: true,
            data: result[0]
        });
    });
});

/**
 * 更新文档 - PUT /documents/:id
 * @description 更新指定UUID的文档信息
 * @param {string} id - 文档UUID
 * @body {Object} document - 要更新的文档信息
 * @body {string} [document.name] - 文档名称
 * @body {string} [document.version] - 版本号
 * @body {string} [document.version_description] - 版本描述
 * @body {string} [document.content] - 文档内容
 * @body {string} [document.category] - 文档分类
 * @body {boolean} [document.is_published] - 是否发布
 * @body {string} [document.updated_by] - 更新人
 * @body {string} [document.tags] - 标签
 * @body {string} [document.file_path] - 关联文件路径
 * @returns {Object} 更新结果
 */
router.put('/:id', (req, res) => {
    console.log(req.params,'params...');
    
    const { id } = req.params;
    const {
        name,
        version,
        version_description,
        content,
        category,
        is_published,
        updated_by,
        tags,
        file_path
    } = req.body;

    // UUID格式验证
    if (!isValidUUID(id)) {
        return res.status(400).json({
            success: false,
            message: '无效的文档ID格式'
        });
    }

    // 先检查文档是否存在
    const checkSql = 'SELECT id FROM documents WHERE id = ?';
    
    connection.query(checkSql, [id], (err, result) => {
        if (err) {
            console.error('检查文档存在性失败:', err);
            return res.status(500).json({
                success: false,
                message: '更新失败',
                error: err.message
            });
        }

        if (result.length === 0) {
            return res.status(404).json({
                success: false,
                message: '文档不存在'
            });
        }

        // 构建更新字段和值
        let updateFields = [];
        let updateValues = [];

        if (name !== undefined) {
            updateFields.push('name = ?');
            updateValues.push(name);
        }
        if (version !== undefined) {
            updateFields.push('version = ?');
            updateValues.push(version);
        }
        if (version_description !== undefined) {
            updateFields.push('version_description = ?');
            updateValues.push(version_description);
        }
        if (content !== undefined) {
            updateFields.push('content = ?');
            updateValues.push(content);
        }
        if (category !== undefined) {
            updateFields.push('category = ?');
            updateValues.push(category);
        }
        if (is_published !== undefined) {
            updateFields.push('is_published = ?');
            updateValues.push(is_published);
        }
        if (updated_by !== undefined) {
            updateFields.push('updated_by = ?');
            updateValues.push(updated_by);
        }
        if (tags !== undefined) {
            updateFields.push('tags = ?');
            updateValues.push(tags);
        }
        if (file_path !== undefined) {
            updateFields.push('file_path = ?');
            updateValues.push(file_path);
        }

        if (updateFields.length === 0) {
            return res.status(400).json({
                success: false,
                message: '没有提供要更新的字段'
            });
        }

        // 执行更新（updated_at 由触发器自动更新）
        const updateSql = `UPDATE documents SET ${updateFields.join(', ')} WHERE id = ?`;
        updateValues.push(id);

        connection.query(updateSql, updateValues, (err, result) => {
            if (err) {
                console.error('更新文档失败:', err);
                return res.status(500).json({
                    success: false,
                    message: '更新失败',
                    error: err.message
                });
            }

            res.json({
                success: true,
                message: '文档更新成功',
                data: {
                    id: id, // UUID字符串，不需要parseInt
                    affected_rows: result.affectedRows
                }
            });
        });
    });
});

/**
 * 删除文档 - DELETE /documents/:id
 * @description 删除指定UUID的文档
 * @param {string} id - 文档UUID
 * @returns {Object} 删除结果
 */
router.delete('/:id', (req, res) => {
    const { id } = req.params;

    // UUID格式验证
    if (!isValidUUID(id)) {
        return res.status(400).json({
            success: false,
            message: '无效的文档ID格式'
        });
    }

    // 先检查文档是否存在
    const checkSql = 'SELECT id, name FROM documents WHERE id = ?';
    
    connection.query(checkSql, [id], (err, result) => {
        if (err) {
            console.error('检查文档存在性失败:', err);
            return res.status(500).json({
                success: false,
                message: '删除失败',
                error: err.message
            });
        }

        if (result.length === 0) {
            return res.status(404).json({
                success: false,
                message: '文档不存在'
            });
        }

        const documentName = result[0].name;

        // 执行删除
        const deleteSql = 'DELETE FROM documents WHERE id = ?';
        
        connection.query(deleteSql, [id], (err, result) => {
            if (err) {
                console.error('删除文档失败:', err);
                return res.status(500).json({
                    success: false,
                    message: '删除失败',
                    error: err.message
                });
            }

            res.json({
                success: true,
                message: `文档 "${documentName}" 删除成功`,
                data: {
                    id: id, // UUID字符串，不需要parseInt
                    deleted_rows: result.affectedRows
                }
            });
        });
    });
});

/**
 * 批量操作接口
 */

/**
 * 批量删除文档 - DELETE /documents/batch
 * @description 批量删除多个文档
 * @body {Array<string>} ids - 要删除的文档UUID数组
 * @returns {Object} 批量删除结果
 */
router.delete('/batch/delete', (req, res) => {
    const { ids } = req.body;

    if (!Array.isArray(ids) || ids.length === 0) {
        return res.status(400).json({
            success: false,
            message: '请提供有效的文档ID数组'
        });
    }

    // 验证所有ID都是有效的UUID格式
    const validIds = ids.filter(id => isValidUUID(id));
    if (validIds.length !== ids.length) {
        return res.status(400).json({
            success: false,
            message: '包含无效的文档ID格式'
        });
    }

    const placeholders = validIds.map(() => '?').join(',');
    const deleteSql = `DELETE FROM documents WHERE id IN (${placeholders})`;

    connection.query(deleteSql, validIds, (err, result) => {
        if (err) {
            console.error('批量删除文档失败:', err);
            return res.status(500).json({
                success: false,
                message: '批量删除失败',
                error: err.message
            });
        }

        res.json({
            success: true,
            message: `成功删除 ${result.affectedRows} 个文档`,
            data: {
                requested_count: validIds.length,
                deleted_count: result.affectedRows
            }
        });
    });
});

/**
 * 批量更新发布状态 - PUT /documents/batch/publish
 * @description 批量更新文档的发布状态
 * @body {Array<string>} ids - 要更新的文档UUID数组
 * @body {boolean} is_published - 发布状态
 * @body {string} [updated_by] - 更新人
 * @returns {Object} 批量更新结果
 */
router.put('/batch/publish', (req, res) => {
    const { ids, is_published, updated_by } = req.body;

    if (!Array.isArray(ids) || ids.length === 0) {
        return res.status(400).json({
            success: false,
            message: '请提供有效的文档ID数组'
        });
    }

    if (typeof is_published !== 'boolean') {
        return res.status(400).json({
            success: false,
            message: '请提供有效的发布状态'
        });
    }

    const validIds = ids.filter(id => isValidUUID(id));
    if (validIds.length !== ids.length) {
        return res.status(400).json({
            success: false,
            message: '包含无效的文档ID格式'
        });
    }

    const placeholders = validIds.map(() => '?').join(',');
    let updateSql = `UPDATE documents SET is_published = ?`;
    let queryParams = [is_published];

    if (updated_by) {
        updateSql += ', updated_by = ?';
        queryParams.push(updated_by);
    }

    updateSql += ` WHERE id IN (${placeholders})`;
    queryParams.push(...validIds);

    connection.query(updateSql, queryParams, (err, result) => {
        if (err) {
            console.error('批量更新发布状态失败:', err);
            return res.status(500).json({
                success: false,
                message: '批量更新失败',
                error: err.message
            });
        }

        res.json({
            success: true,
            message: `成功更新 ${result.affectedRows} 个文档的发布状态`,
            data: {
                requested_count: validIds.length,
                updated_count: result.affectedRows,
                is_published
            }
        });
    });
});


module.exports = router;